using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Common.Database
{
    /// <summary>
    /// 
    /// </summary>
    public class TableSchemaDALC
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="columnChangeType"></param>
        /// <param name="colName"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static bool ChangeTableSchema(string connStr, string tblName, TableColumnSchemaChangeType columnChangeType, string colName, FieldDataType dbType)
        {
            SqlConnection conn = new SqlConnection(connStr);
            System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope();
            bool succeed = false;
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string sql = "Alter table [" + tblName + "] ";
                switch(columnChangeType)
                {
                    case TableColumnSchemaChangeType.Add:
                        sql += "Add Column [" + colName + "] " + FieldDataType.ToMssqlFieldDataTypeString(dbType);
                        break;
                    case TableColumnSchemaChangeType.DbTypeChange:
                        sql += "Alter Column [" + colName + "] " + FieldDataType.ToMssqlFieldDataTypeString(dbType);
                        break;
                    case TableColumnSchemaChangeType.Drop:
                        sql += "Drop Column [" + colName + "]";
                        break;
                }
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                succeed = true;
                scope.Complete();
            }
            catch(Exception ex)
            {
                succeed = false;
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                scope.Dispose();
                conn.Close();
            }
            return succeed;
        }
    }
}
